import os,re
import lxml.etree as etree
from pyquery import PyQuery as pq
import sqlite3,os

#https://jingyan.baidu.com/article/a3761b2bc78d731576f9aaa5.html
#天猫国际
def Tmall_International(selector):
    my_dict = {'标题': '',
               '店铺名': '',
               '链接': '',
               '销量': '',
               '新品': '',
               '快递费': '',
               '店铺分A': '',
               '店铺分B': '',
               '店铺分C': '',
               'dsrIconA': '',
               'dsrIconB': '',
               'dsrIconC': '',
               '全球购': '',
               '金牌卖家': ''
               }

    title = selector.xpath('/html/head/title')[0].text
    try:
        tmall_url = selector.xpath('//*[@rel="canonical"]')[0].attrib['href']
    except:
        tmall_url = '无数据'
    try:
        tmail_shop_name = selector.xpath('//*[@class="slogo-shopname"]/strong')[0].text
    except:
        tmail_shop_name = '无数据'
    try:
        tmall_Sales = selector.xpath('//span[contains(text(),"销量")]/following-sibling::span[1]')[0].text
    except:
        tmall_Sales = '无数据'
    try:
        tmall_New_product = selector.xpath('//li[@title="“天猫新品”是指天猫首次上架的特定商品"]')[0].attrib['title']
    except:
        tmall_New_product = '无数据'
    try:
        tmall_Freight = selector.xpath('//p[contains(text(),"运费")]/em')[0].text
    except:
        tmall_Freight = '无数据'
    try:
        tmall_scoreA = selector.xpath('//*[@class="shopdsr-score-con"]')[0].text
    except:
        tmall_scoreA = '无数据'
    try:
        tmall_scoreB = selector.xpath('//*[@class="shopdsr-score-con"]')[1].text
    except:
        tmall_scoreB = '无数据'
    try:
        tmall_scoreC = selector.xpath('//*[@class="shopdsr-score-con"]')[2].text
    except:
        tmall_scoreC = '无数据'
    try:
        tmall_statusA = selector.xpath('//*[@class="shopdsr-score-con"]/../s')[0].attrib['class']
    except:
        tmall_statusA = '无数据'
    try:
        tmall_statusB = selector.xpath('//*[@class="shopdsr-score-con"]/../s')[1].attrib['class']
    except:
        tmall_statusB = '无数据'
    try:
        tmall_statusC = selector.xpath('//*[@class="shopdsr-score-con"]/../s')[2].attrib['class']
    except:
        tmall_statusC = '无数据'

    全球购=''
    金牌卖家=''
    my_dict.update({'标题': title})
    my_dict.update({'店铺名': tmail_shop_name})
    my_dict.update({'链接': tmall_url})
    my_dict.update({'销量': tmall_Sales})
    my_dict.update({'新品': tmall_New_product})
    my_dict.update({'快递费': tmall_Freight})
    my_dict.update({'店铺分A': tmall_scoreA})
    my_dict.update({'店铺分B': tmall_scoreB})
    my_dict.update({'店铺分C': tmall_scoreC})
    my_dict.update({'dsrIconA': tmall_statusA})
    my_dict.update({'dsrIconB': tmall_statusB})
    my_dict.update({'dsrIconC': tmall_statusC})
    my_dict.update({'全球购': 全球购})
    my_dict.update({'金牌卖家': 金牌卖家})
    return my_dict
#天猫店
def Tmall_Parsing(selector):
    my_dict = {'标题': '',
               '店铺名': '',
               '链接': '',
               '销量': '',
               '新品': '',
               '快递费': '',
               '店铺分A': '',
               '店铺分B': '',
               '店铺分C': '',
               'dsrIconA': '',
               'dsrIconB': '',
               'dsrIconC': '',
               '全球购': '',
               '金牌卖家': ''

               }

    title=selector.xpath('/html/head/title')[0].text
    try:
        tmall_url=selector.xpath('//*[@rel="canonical"]')[0].attrib['href']
    except:
        tmall_url='无数据'
    try:
        tmail_shop_name=selector.xpath('//*[@class="slogo-shopname"]/strong')[0].text
    except:
        tmail_shop_name='无数据'
    try:
        tmall_Sales=selector.xpath('//span[contains(text(),"销量")]/following-sibling::span[1]')[0].text
    except:
        tmall_Sales='无数据'
    try:
        tmall_New_product=selector.xpath('//li[@title="“天猫新品”是指天猫首次上架的特定商品"]')[0].attrib['title']
    except:
        tmall_New_product='无数据'
    try:
        tmall_Freight=selector.xpath('//span[contains(text(),"快递")]')[0].text
    except:
        tmall_Freight='无数据'
    try:
        tmall_scoreA=selector.xpath('//*[@class="shopdsr-score-con"]')[0].text
    except:
        tmall_scoreA='无数据'
    try:
        tmall_scoreB = selector.xpath('//*[@class="shopdsr-score-con"]')[1].text
    except:
        tmall_scoreB='无数据'
    try:
        tmall_scoreC = selector.xpath('//*[@class="shopdsr-score-con"]')[2].text
    except:
        tmall_scoreC='无数据'
    try:
        tmall_statusA=selector.xpath('//*[@class="shopdsr-score-con"]/../s')[0].attrib['class']
    except:
        tmall_statusA='无数据'
    try:
        tmall_statusB = selector.xpath('//*[@class="shopdsr-score-con"]/../s')[1].attrib['class']
    except:
        tmall_statusB='无数据'
    try:
        tmall_statusC = selector.xpath('//*[@class="shopdsr-score-con"]/../s')[2].attrib['class']
    except:
        tmall_statusC='无数据'
    全球购 = ''
    金牌卖家 = ''
    my_dict.update({'标题':title})
    my_dict.update({'店铺名': tmail_shop_name})
    my_dict.update({'链接': tmall_url})
    my_dict.update({'销量': tmall_Sales})
    my_dict.update({'新品': tmall_New_product})
    my_dict.update({'快递费': tmall_Freight})
    my_dict.update({'店铺分A': tmall_scoreA})
    my_dict.update({'店铺分B': tmall_scoreB})
    my_dict.update({'店铺分C': tmall_scoreC})
    my_dict.update({'dsrIconA': tmall_statusA})
    my_dict.update({'dsrIconB': tmall_statusB})
    my_dict.update({'dsrIconC': tmall_statusC})
    my_dict.update({'全球购': 全球购})
    my_dict.update({'金牌卖家': 金牌卖家})
    return my_dict
#全球购-淘宝
def Global_purchase(selector,doc):
    my_dict = {'标题': '',
               '店铺名': '',
               '链接': '',
               '销量': '',
               '新品': '',
               '快递费': '',
               '店铺分A': '',
               '店铺分B': '',
               '店铺分C': '',
               'dsrIconA': '',
               'dsrIconB': '',
               'dsrIconC': '',
               '全球购':'',
               '金牌卖家':''
               }

    title = selector.xpath('/html/head/title')[0].text
    try:
        tmall_url = selector.xpath('//*[@rel="canonical"]')[0].attrib['href']
    except:
        tmall_url = '无数据'
    try:
        tmail_shop_name = doc('.tb-shop-name a').attr('title')
    except:
        tmail_shop_name = '无数据'
    try:
        tmall_Sales = doc('#J_RateCounter')[0].text
    except:
        tmall_Sales = '无数据'
    try:
        tmall_New_product = selector.xpath('//a[text()="新品"]')
    except:
        tmall_New_product = '无数据'
    try:
        tmall_Freight = selector.xpath('//span[contains(text(),"快递")]')[0].text
    except:
        tmall_Freight = '无数据'
    try:
        tmall_scoreA = re.findall('\d+.\d+', selector.xpath('//dt[contains(text(),"描述")]/../dd/a')[0].text)
    except:
        tmall_scoreA = '无数据'
    try:
        tmall_scoreB = re.findall('\d+.\d+', selector.xpath('//dt[contains(text(),"服务")]/../dd/a')[0].text)[0]
    except:
        tmall_scoreB = '无数据'
    try:
        tmall_scoreC = re.findall('\d+.\d+', selector.xpath('//dt[contains(text(),"物流")]/../dd/a')[0].text)[0]
    except:
        tmall_scoreC = '无数据'
    try:
        tmall_statusA = selector.xpath('//dt[contains(text(),"描述")]/../dd')[0].attrib['class']
    except:
        tmall_statusA = '无数据'
    try:
        tmall_statusB = selector.xpath('//dt[contains(text(),"服务")]/../dd')[0].attrib['class']
    except:
        tmall_statusB = '无数据'
    try:
        tmall_statusC = selector.xpath('//dt[contains(text(),"物流")]/../dd')[0].attrib['class']
    except:
        tmall_statusC = '无数据'
    if '搜全球购' in doc.text():
        全球购=True
    else:
        全球购=False
    try:
        if selector.xpath('//a[contains(@title, "金牌卖家")]') == []:
            金牌卖家 = ''
        else:
            金牌卖家='金牌卖家'
    except:
        金牌卖家 = ''


    my_dict.update({'标题': title})
    my_dict.update({'店铺名': tmail_shop_name})
    my_dict.update({'链接': tmall_url})
    my_dict.update({'销量': tmall_Sales})
    my_dict.update({'新品': tmall_New_product})
    my_dict.update({'快递费': tmall_Freight})
    my_dict.update({'店铺分A': tmall_scoreA})
    my_dict.update({'店铺分B': tmall_scoreB})
    my_dict.update({'店铺分C': tmall_scoreC})
    my_dict.update({'dsrIconA': tmall_statusA})
    my_dict.update({'dsrIconB': tmall_statusB})
    my_dict.update({'dsrIconC': tmall_statusC})
    my_dict.update({'全球购': 全球购})
    my_dict.update({'金牌卖家': 金牌卖家})
    return my_dict
#极有家、企业店-淘宝
def Extremely_homely(selector,doc):
    my_dict = {'标题': '',
               '店铺名': '',
               '链接': '',
               '销量': '',
               '新品': '',
               '快递费': '',
               '店铺分A': '',
               '店铺分B': '',
               '店铺分C': '',
               'dsrIconA': '',
               'dsrIconB': '',
               'dsrIconC': '',
               '全球购': '',
               '金牌卖家': ''
               }

    title = selector.xpath('/html/head/title')[0].text
    try:
        tmall_url = selector.xpath('//*[@rel="canonical"]')[0].attrib['href']
    except:
        tmall_url = '无数据'
    try:
        tmail_shop_name = doc('.shop-name-link').attr('title')
    except:
        tmail_shop_name = '无数据'
    try:
        tmall_Sales = doc('#J_RateCounter')[0].text
    except:
        tmall_Sales = '无数据'
    try:
        tmall_New_product = selector.xpath('//a[text()="新品"]')
    except:
        tmall_New_product = '无数据'
    try:
        tmall_Freight = selector.xpath('//span[contains(text(),"快递")]')[0].text
    except:
        tmall_Freight = '无数据'
    try:
        tmall_scoreA = re.findall('\d+.\d+', selector.xpath('//span[contains(text(),"描述")]/../span/em')[0].text)
    except:
        tmall_scoreA = '无数据'
    try:
        tmall_scoreB = re.findall('\d+.\d+', selector.xpath('//span[contains(text(),"服务")]/../span/em')[0].text)[0]
    except:
        tmall_scoreB = '无数据'
    try:
        tmall_scoreC = re.findall('\d+.\d+', selector.xpath('//span[contains(text(),"物流")]/../span/em')[0].text)[0]
    except:
        tmall_scoreC = '无数据'
    try:
        tmall_statusA = selector.xpath('//span[contains(text(),"描述")]/../span/i')[0].attrib['class']
    except:
        tmall_statusA = '无数据'
    try:
        tmall_statusB = selector.xpath('//span[contains(text(),"服务")]/../span/i')[0].attrib['class']
    except:
        tmall_statusB = '无数据'
    try:
        tmall_statusC = selector.xpath('//span[contains(text(),"物流")]/../span/i')[0].attrib['class']
    except:
        tmall_statusC = '无数据'
    if doc('.shop-summary.J_TShopSummary')!=[]:
        全球购 = '企业店'
    else:
        全球购 = ''
    try:
        if doc('[class="J_TGoldlog jinpai-v"]') == []:
            金牌卖家 = ''
        else:
            金牌卖家 = '金牌卖家'
    except:
        金牌卖家 = ''

    my_dict.update({'标题': title})
    my_dict.update({'店铺名': tmail_shop_name})
    my_dict.update({'链接': tmall_url})
    my_dict.update({'销量': tmall_Sales})
    my_dict.update({'新品': tmall_New_product})
    my_dict.update({'快递费': tmall_Freight})
    my_dict.update({'店铺分A': tmall_scoreA})
    my_dict.update({'店铺分B': tmall_scoreB})
    my_dict.update({'店铺分C': tmall_scoreC})
    my_dict.update({'dsrIconA': tmall_statusA})
    my_dict.update({'dsrIconB': tmall_statusB})
    my_dict.update({'dsrIconC': tmall_statusC})
    my_dict.update({'全球购': 全球购})
    my_dict.update({'金牌卖家': 金牌卖家})
    return my_dict

def get_data(path):


    html_file=open(path,'r',encoding='utf-8')
    html=html_file.read()
    selector = etree.HTML(html)
    doc=pq(html)
    title=selector.xpath('/html/head/title')[0].text
    #普通天猫店
    if 'tmall.com天猫' in title:
        print('普通天猫店')
        my_dict=Tmall_Parsing(selector)
    #天猫国际店
    elif 'tmall.hk天猫国际' in title:
        print('天猫国际店')
        my_dict = Tmall_International(selector)
    #天猫超市店

    #淘宝店
    elif '-淘宝网' in title:
        #如果是全球购
        if '搜全球购' in doc.text():
            my_dict =Global_purchase(selector,doc)
            print('全球购')
        # 如果是极有家店
        elif '优质网店' in doc.text():
            print('极有家')
            my_dict=Extremely_homely(selector, doc)
        #企业店铺
        elif doc('.shop-summary.J_TShopSummary')!=[]:
            print('企业店')
            my_dict = Extremely_homely(selector, doc)
        else:
            selector.xpath('//*[@rel="canonical"]')[0].attrib['href']
            print('普通C店')
            my_dict = Global_purchase(selector, doc)
    return my_dict


def bianli(rootDir):
    data_list = []
    for root,dirs,files in os.walk(rootDir):
        for file in files:
            path=os.path.join(root,file)
            # print(path)
            if 'item.htm' in path:
                print(path)
                data=get_data(path)
                data_list.append(data)
        for dir in dirs:
            bianli(dir)
    return data_list

rootDir=r'D:\易刊蝦皮助理國際版\AppExt\Cache\html1'
sFileName=rootDir.replace('\"','')
sqlit_file=(r"%s\查找新商品.sqlite")%(os.path.abspath(os.path.join(os.path.dirname(sFileName))))
cn=sqlite3.connect(sqlit_file)
cursor=cn.cursor()
try:
    cursor.execute('DROP TABLE 查找新商品')
    cursor.execute('CREATE TABLE 查找新商品(ID INTEGER PRIMARY KEY AUTOINCREMENT,店铺名 TEXT,标题 TEXT,链接 TEXT,销量 TEXT,新品 TEXT,快递费 TEXT,店铺分A TEXT,店铺分B TEXT,店铺分C TEXT,dsrIconA TEXT,dsrIconB TEXT,dsrIconC TEXT,全球购 TEXT,金牌卖家 TEXT)')
except:
    cursor.execute('CREATE TABLE 查找新商品(ID INTEGER PRIMARY KEY AUTOINCREMENT,店铺名 TEXT,标题 TEXT,链接 TEXT,销量 TEXT,新品 TEXT,快递费 TEXT,店铺分A TEXT,店铺分B TEXT,店铺分C TEXT,dsrIconA TEXT,dsrIconB TEXT,dsrIconC TEXT,全球购 TEXT,金牌卖家 TEXT)')
data_list=bianli((rootDir))
for i in data_list:
    try:
        yuju = 'INSERT INTO 查找新商品(店铺名,标题,链接,销量,新品,快递费,店铺分A,店铺分B,店铺分C,dsrIconA,dsrIconB,dsrIconC,全球购,金牌卖家) VALUES ("%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s","%s")'\
    %(i['店铺名'],i['标题'],i['链接'],i['销量'],i['新品'],i['快递费'],i['店铺分A'],i['店铺分B'],i['店铺分C'],i['dsrIconA'],i['dsrIconB'],i['dsrIconC'],i['全球购'],i['金牌卖家'])
        cursor.execute(yuju)
    except:
        print()
    # yuju=yuju.replace('\'', '\"')


cn.commit()
print('数据库路径是：%s'%sqlit_file)